CREATE TRIGGER [dbo].[trgAfterUpdate]     
ON  [dbo].[LeadStatus]     
AFTER UPDATE  
AS   
BEGIN   
-- SET NOCOUNT ON added to prevent extra result sets from   
-- interfering with SELECT statements.   
SET NOCOUNT ON;   
declare @purchaseorderID int;   
declare @OriginalPurchaseOrderID int;   
declare @leadid int;   
declare @TotalLeadsSupplied int;   
declare @Accepted int;   
declare @Returned int;   
declare @InvalidReturn int;   
declare @Replacement int;   
declare @CallCentreID int;   
declare @CurrentPurchaseOrderID int;      
-- Must decide which PurchaseOrder to allocate to   
select @leadid=i.leadid from inserted i;   
select @CallCentreID=i.callcentreid from inserted i;   
select @purchaseorderID=i.purchaseorderid from inserted i;   
select @OriginalPurchaseOrderID=d.purchaseorderid from deleted d;      
set @CurrentPurchaseOrderID=@purchaseorderID ;   
while (@CurrentPurchaseOrderID!=0)   
begin   
-- Total Leads Supplied   
select @TotalLeadsSupplied=count(*) 
from leadstatus 
where leadid=@leadid and PurchaseOrderID=@CurrentPurchaseOrderID and leadflagsid>1 and replacement is null;     
-- Accepted   
select @Accepted=count(*) 
from leadstatus 
where leadid=@leadid and PurchaseOrderID=@CurrentPurchaseOrderID and leadflagsid in(2,3,4) ;     
-- Return   
select @Returned=count(*) 
from leadstatus 
where leadid=@leadid and PurchaseOrderID=@CurrentPurchaseOrderID and leadflagsid in(8,5,6);     
-- Invalid Return   
select @InvalidReturn=count(*) 
from leadstatus 
where leadid=@leadid and PurchaseOrderID=@CurrentPurchaseOrderID and leadflagsid in(6) ;     
-- Replacement   
select @Replacement=count(*) 
from leadstatus 
where leadid=@leadid and PurchaseOrderID=@CurrentPurchaseOrderID and Replacement is not null and leadstatus.leadflagsid>1;      
update dbo.purchaseorders set Accepted=@Accepted, Rejected=@Returned, InvalidReturns=@InvalidReturn, 
	Replaced=@Replacement,    Deficit=quantity-(@TotalLeadsSupplied-@Returned+@Replacement+@InvalidReturn),    
	TotalLeadsSupplied=@TotalLeadsSupplied, dateupdated=getdate()    
where purchaseorderid=@CurrentPurchaseOrderID       
if( @CurrentPurchaseOrderID=@OriginalPurchaseOrderID or @OriginalPurchaseOrderID=null)     
	set @CurrentPurchaseOrderID=0;   
else    
	set @CurrentPurchaseOrderID=@OriginalPurchaseOrderID ;     
end  
END  